package net.hn.hnms.biz.safety.location.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import net.hn.hnms.biz.api.feign.model.DataStateVo;
import net.hn.hnms.biz.api.mine.RemoteMineInfoService;
import net.hn.hnms.biz.api.mine.model.SysModelVo;
import net.hn.hnms.biz.api.plan.RemoteOnDutyService;
import net.hn.hnms.biz.api.plan.model.OnDutyVo;
import net.hn.hnms.biz.api.safety.location.model.*;
import net.hn.hnms.biz.api.safety.location.model.dto.CurveDto;
import net.hn.hnms.biz.api.safety.monitor.model.BaseSensorCoordDto;
import net.hn.hnms.biz.safety.location.domain.*;
import net.hn.hnms.biz.safety.location.group.domain.BasePersonnelInfoVo;
import net.hn.hnms.biz.safety.location.mapper.*;
import net.hn.hnms.biz.safety.location.repository.DutyLeadEntityRepository;
import net.hn.hnms.biz.safety.location.repository.JxryHisPersonnelinfoRepository;
import net.hn.hnms.biz.safety.location.repository.JxryNowPersonnelinfoRepository;
import net.hn.hnms.sys.common.core.constant.Constants;
import net.hn.hnms.sys.common.core.domain.R;
import net.hn.hnms.sys.common.core.domain.ResponsePage;
import net.hn.hnms.sys.common.redis.utils.RedisUtils;
import net.hn.hnms.sys.common.core.utils.DateUtils;
import net.hn.hnms.sys.system.api.RemoteUserService;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.net.ftp.FTP;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;
import org.apache.tika.Tika;
import org.apache.tika.exception.TikaException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.*;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Duration;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * Author: PanSX
 * Date: 2022/8/9 10:34
 */
@Service
@RequiredArgsConstructor
@Slf4j
public class LocationServiceImpl implements LocationService {
    private final JdbcTemplate locationJdbcTemplate;
    private final DutyLeadEntityRepository dutyLeadEntityRepository;
    private final RemoteMineInfoService remoteMineInfoService;
    private final JxryNowPersonnelinfoRepository jxryNowPersonnelinfoRepository;
    private final JxryHisPersonnelinfoRepository jxryHisPersonnelinfoRepository;
    @Autowired
    private RemoteUserService remoteUserService;
    private final RemoteOnDutyService remoteOnDutyService;

    @Resource
    private TramNowMapper tramNowMapper;

    //人员基本信息表
    @Resource
    private BasePersonnelInfoMapper basePersonnelInfoMapper;

    @Resource
    private DwdJxryBaseStationInfoMapper dwdJxryBaseStationInfoMapper;
    @Value("${spring.profiles.active}")
    private String profile;

    /**
     * 井下人员分站信息Mapper
     */
    @Resource
    private JxryBaseStationInfoMapper jxryBaseStationInfoMapper;

    @Resource
    private DwdJxryHisPersonnelinfoMapper dwdJxryHisPersonnelinfoMapper;
    @Resource
    private DwdJxryNowPersonnelinfoMapper dwdJxryNowPersonnelinfoMapper;

    @Override
    public LocationTodayInfo todayInfo(String mineCode) {
        LocationTodayInfo locationTodayInfo = new LocationTodayInfo();


        try {
            if ("150781013603".equals(mineCode) || "150781013670".equals(mineCode) || "150781013194".equals(mineCode)) {
                R<OnDutyVo> onDuty = remoteOnDutyService.getOnDuty(null, mineCode);
                if (onDuty.getCode() == 200) {
                    List leads = new ArrayList();
                    leads.add(onDuty.getData().getLeadPersonNames());
                    locationTodayInfo.setLead(leads);
                    locationTodayInfo.setDuty(onDuty.getData().getDutyPersonNames());
                }
            } else {
                Map<String, String> stringStringMap = ftpUtile(mineCode);
                List leads = new ArrayList();
                leads.add(stringStringMap.get("lead"));
                locationTodayInfo.setLead(leads);
                locationTodayInfo.setDuty(stringStringMap.get("duty"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }

        String currentNumberSql = "select count(1) from dwd_jxry_now_personnelinfo where" +
                " flag_bit='1' and data_time > CURRENT_DATE and data_time = " +
                "( SELECT MAX (data_time ) FROM \"dwd_jxry_now_personnelinfo\" where 1=1 %s ) %s ";
        try {
            Integer currentNumber = locationJdbcTemplate.queryForObject(String.format(currentNumberSql, mineCode, mineCode), Integer.class);


            locationTodayInfo.setPeople(currentNumber == null ? 0 : currentNumber);
        } catch (EmptyResultDataAccessException e) {
            locationTodayInfo.setPeople(0);
        }
        String alarmSql = "SELECT count(1) FROM %s " +
                " where in_station_time>CURRENT_DATE %s";
        try {
            //限制区域
            Integer currentNumber = locationJdbcTemplate.queryForObject(String.format(alarmSql, "dwd_jxry_base_accessrestrictedarea", mineCode), Integer.class);
            locationTodayInfo.setProhibitedArea(currentNumber == null ? 0 : currentNumber);
        } catch (EmptyResultDataAccessException e) {
            locationTodayInfo.setProhibitedArea(0);
        }
        try {
            //求救
            Integer currentNumber = locationJdbcTemplate.queryForObject(String.format(alarmSql, "dwd_jxry_base_cryinfo", mineCode), Integer.class);
            locationTodayInfo.setSos(currentNumber == null ? 0 : currentNumber);
        } catch (EmptyResultDataAccessException e) {
            locationTodayInfo.setSos(0);
        }
        try {
            //超时
            Integer currentNumber = locationJdbcTemplate.queryForObject(String.format(alarmSql, "dwd_jxry_base_timeoutalarm", mineCode), Integer.class);
            locationTodayInfo.setTimeoutArea(currentNumber == null ? 0 : currentNumber);
        } catch (EmptyResultDataAccessException e) {
            locationTodayInfo.setTimeoutArea(0);
        }
        try {
            //超员
            Integer currentNumber = locationJdbcTemplate.queryForObject(String.format(alarmSql, "dwd_jxry_base_overmanalarm", mineCode), Integer.class);
            locationTodayInfo.setOverload(currentNumber == null ? 0 : currentNumber);
        } catch (EmptyResultDataAccessException e) {
            locationTodayInfo.setOverload(0);
        }
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//            sdf.format(new Date())
            String beginTime = "";
            Calendar instance = Calendar.getInstance();
            if (instance.get(Calendar.HOUR_OF_DAY) > 5) {
                beginTime = sdf.format(instance.getTime()) + " 05:00:00";
            } else {
                instance.set(Calendar.DAY_OF_MONTH, instance.get(Calendar.DAY_OF_MONTH) - 1);
                beginTime = sdf.format(instance.getTime()) + " 05:00:00";
            }
            String personCountSql = "SELECT count(distinct staff_code) FROM \"dwd_jxry_now_personnelinfo\"" +
                    " where in_time>'" + beginTime + "' %s";
            Integer personCount = locationJdbcTemplate.queryForObject(String.format(personCountSql, mineCode), Integer.class);
            locationTodayInfo.setPersonCount(personCount == null ? 0 : personCount);
        } catch (EmptyResultDataAccessException e) {
            locationTodayInfo.setPersonCount(0);
        }

        return locationTodayInfo;
    }

    @Override
    public List<LeadVo> leads(String mineCode) {
        String sql = "SELECT " +
                "	staff_code as code, " +
                "	staff_name as name , " +
                "	working_condition as workingCondition , " +
                "	in_time as startTime, " +
                "	MAX ( out_time ) as endTime , " +
                "	EXTRACT(EPOCH FROM (MAX ( out_time )-  in_time)) as duration " +
                "FROM " +
                "	\"dwd_jxry_now_personnelinfo\"  " +
                "WHERE " +
                "	mine_leader = '1'  " +
                "	AND data_time > CURRENT_DATE  %s" +
                "GROUP BY " +
                "	staff_code, " +
                "	staff_name, " +
                "	in_time, " +
                "	working_condition";
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        List<LeadVo> leadVos = locationJdbcTemplate.query(String.format(sql, mineCode), new Object[]{}, new BeanPropertyRowMapper<LeadVo>(LeadVo.class));
        for (LeadVo leadVo : leadVos) {
            if (leadVo.getEndTime() == null) {
                leadVo.setDuration((new Date().getTime() - leadVo.getStartTime().getTime()) / 1000);
            }
        }
        return leadVos;
    }

    @Override
    public PeopleTrendVo peopleTrend(String type, String mineCode) {
        PeopleTrendVo peopleTrendVo = new PeopleTrendVo();
        List<String> xDate = new ArrayList<>();
        if ("150781013671".equals(mineCode)) {
            //灵露煤矿 特殊处理 查询历史数据
            //获取今天的日期
            Date now = new Date();
            //获取开始日期
            Date startDate = null;
            if ("周".equals(type)) {
                //默认七天
                startDate = DateUtils.addDays(now, -7);
            } else if ("月".equals(type)) {
                startDate = DateUtils.addMonths(now, -1);
            } else {
                startDate = DateUtils.addDays(now, -7);
            }
            // 转换 Date 到 LocalDate
            LocalDate startLocalDate = startDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            LocalDate nowLocalDate = now.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            //判断开始时间到现在有多少天
            // 计算天数
            long days = ChronoUnit.DAYS.between(startLocalDate, nowLocalDate);
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            List<Integer> y = new ArrayList<>();
            for (int i = 0; i < days; i++) {
                Date date = DateUtils.addDays(startDate, i);
                String format = dateFormat.format(date);
                xDate.add(format);
                //获取当天的所有数据
                CurveDto curveDto =dwdJxryHisPersonnelinfoMapper.selectMaxAndMinPersonByDay(format, mineCode);
                if (curveDto != null && curveDto.getMaxValue() != null) {
                    y.add(Integer.valueOf(curveDto.getMaxValue()));
                } else {
                    y.add(0);
                }
            }
            peopleTrendVo.setXData(xDate);
            peopleTrendVo.setCount(y.stream().mapToInt(Integer::intValue).toArray());
            return peopleTrendVo;
        }

        Calendar calendar = Calendar.getInstance();
        Date endTime = calendar.getTime();
        if ("月".equals(type)) {
            calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH) - 1);
        } else {
            calendar.set(Calendar.DAY_OF_MONTH, calendar.get(Calendar.DAY_OF_MONTH) - 7);
        }

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        while (calendar.getTime().getTime() <= endTime.getTime()) {
            xDate.add(sdf.format(calendar.getTime()));
            calendar.set(Calendar.DAY_OF_MONTH, calendar.get(Calendar.DAY_OF_MONTH) + 1);
        }
        peopleTrendVo.setXData(xDate);
        peopleTrendVo.setCount(new int[xDate.size()]);
        String sql = "SELECT number_people_max,to_char(data_time,'yyyy-mm-dd') FROM " +
                "\"dwd_jxry_miners_numberinfo\" where data_time>='%s' %s";
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        List<Map<String, Object>> maps = locationJdbcTemplate.queryForList(String.format(sql, xDate.get(0), mineCode));
        maps.forEach(s -> {
            peopleTrendVo.getCount()[xDate.indexOf(s.get("to_char"))] = s.get("number_people_max") == null ? 0 : (int) s.get("number_people_max");
        });
        return peopleTrendVo;
    }

    @Override
    public List<PeopleVo> peopleInfo(String name, String mineCode) {
        String sql = "	SELECT\n" +
                "		staff_code AS userCoed,\n" +
                "		staff_name AS NAME,\n" +
                "		in_time AS intoTime,\n" +
                "	 out_time  AS out_time,\n" +
                "		data_time  AS data_time,\n" +
                "		substation_location AS LOCATION,\n" +
                "		working_condition AS status \n" +
                "	FROM\n" +
                "		\"dwd_jxry_now_personnelinfo\" \n" +
                "	WHERE\n" +
                "		data_time = (select max(data_time) from dwd_jxry_now_personnelinfo where 1=1 %s)  %s %s";
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        String format = String.format(sql, mineCode, StringUtils.isBlank(name) ? "" : "and staff_name='" + name + "'", mineCode);
        List<PeopleVo> peopleVos = locationJdbcTemplate.query(format, new Object[]{}, new BeanPropertyRowMapper<>(PeopleVo.class));

        return peopleVos;
    }

    /**
     * BasePersonnelInfoVo
     *
     * @param code
     * @return
     */
    @Override
    public BasePersonnelInfoVo getBasePeopleInfo(String code, String mineCode) {
        LambdaQueryWrapper<BasePersonnelInfoVo> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(BasePersonnelInfoVo::getStaffCode, code);
        wrapper.eq(net.hn.hnms.sys.common.core.utils.StringUtils.isNotBlank(mineCode), BasePersonnelInfoVo::getMineCode, mineCode);
        BasePersonnelInfoVo basePersonnelInfoVo = basePersonnelInfoMapper.selectOne(wrapper);
        if (basePersonnelInfoVo == null) {
            return new BasePersonnelInfoVo();
        }
        return basePersonnelInfoVo;
    }

    @Override
    public List<KeyAreaVo> keyAreas(String mineCode) {
        String sql = "SELECT " +
                "	code as areaId, " +
                "	area_name as name " +
                "FROM " +
                "	\"dwd_jxry_now_personnelareainfo\" " +
                "WHERE " +
                "	date_time = ( SELECT MAX ( date_time ) FROM \"dwd_jxry_now_personnelareainfo\" where 1=1 %s ) %s";
        String where = "";
        if (StringUtils.isNotBlank(mineCode)) {
            where = " and mine_code='" + mineCode + "'";
        } else {
            where = "";
        }
        List<KeyAreaVo> query = locationJdbcTemplate.query(String.format(sql, where, where), new Object[]{}, new BeanPropertyRowMapper<>(KeyAreaVo.class));
        query.forEach(s -> {
            s.setPeoples(peopleInfoByArea(s.getAreaId(), null, mineCode));
        });
        return query;
    }

    @Override
    public IndicatorVo indicator(String mineCode) {
        String sql = "SELECT data_time FROM \"dwd_jxry_now_personnelinfo\" where data_time>current_timestamp - interval '10 minutes' %s limit 1";
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        List list = locationJdbcTemplate.queryForList(String.format(sql, mineCode));
        IndicatorVo indicatorVo = new IndicatorVo();
        indicatorVo.setOnline(list.isEmpty() ? "断开" : "正常");

        R<SysModelVo> sys = remoteMineInfoService.getSysModel("人员定位");
        if (Constants.SUCCESS == sys.getCode()) indicatorVo.setSysModel(sys.getData().getSysModel());
        return indicatorVo;
    }

    @Override
    public List<TrailVo> trail(String staffCode, String inTime, String mineCode) {
        List<TrailVo> list = new ArrayList<>();
        //灵露煤矿特殊处理 原因是实时表里的数据是仅有最新一份 实时表里无历史数据
        if ("150781013671".equals(mineCode)) {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            DwdJxryNowPersonnelinfoPo dwdJxryNowPersonnelinfoPo= null;
            LocalDateTime localDateTime = LocalDateTime.parse(inTime, formatter);
            Timestamp timestamp = Timestamp.valueOf(localDateTime);
            //获取当前人记录信息  最新一条 limit 1
             dwdJxryNowPersonnelinfoPo = dwdJxryNowPersonnelinfoMapper.selectOne(new LambdaQueryWrapper<DwdJxryNowPersonnelinfoPo>()
                    .eq(DwdJxryNowPersonnelinfoPo::getStaffCode, staffCode).eq(DwdJxryNowPersonnelinfoPo::getInTime, timestamp)
                    .eq(DwdJxryNowPersonnelinfoPo::getMineCode, mineCode)
                    .last("limit 1"));
            if (dwdJxryNowPersonnelinfoPo != null) {
                //获取的轨迹字段集合
                String xjgjjzsjjh = dwdJxryNowPersonnelinfoPo.getPeopleWay() != null ? dwdJxryNowPersonnelinfoPo.getPeopleWay() : "";
                //拆分基站信息
                //查询当前煤矿下所有基站
                List<JxryBaseStationInfoPo> rydwRyjzPos = jxryBaseStationInfoMapper.selectList(null);
                //转成map
                Map<String, JxryBaseStationInfoPo> rydwRyjzPoMap = rydwRyjzPos.stream()
                        .collect(Collectors.toMap(
                                JxryBaseStationInfoPo::getSubstationCode, // 键为 jzbm 属性
                                p -> Optional.ofNullable(p).orElse(new JxryBaseStationInfoPo()), // 值为 RydwRyjzPo 对象，处理可能为空的情况
                                (existing, replacement) -> existing)); // 解决重复键冲突的策略，保留已存在的值

                //根据逗号拆分 每一个分站
                String[] split = xjgjjzsjjh.split(",");

                for (String s : split) {
                    TrailVo trailVo = new TrailVo();
                    //根据& 区分编码和时间
                    String[] split1 = s.split("&");
                    String stationCode = split1[0];
                    String inStationCodeTime = split1[1];
                    //获取分站详情
                    JxryBaseStationInfoPo rydwRyjzPo = rydwRyjzPoMap.get(stationCode);
                    trailVo.setSubstationCode(stationCode);
                    try {
                        Date parse = simpleDateFormat.parse(inStationCodeTime);
                        trailVo.setInTime(parse);
                    } catch (ParseException e) {
                        throw new RuntimeException(e);
                    }

                    if (rydwRyjzPo != null) {
                        trailVo.setSubstationName(rydwRyjzPo.getSubstationName() + "_" + rydwRyjzPo.getLocalNotes());
                        trailVo.setCoordinate_x(rydwRyjzPo.getCoordinateX() != null ? rydwRyjzPo.getCoordinateX() : new BigDecimal("0"));
                        trailVo.setCoordinate_y(rydwRyjzPo.getCoordinateY() != null ? rydwRyjzPo.getCoordinateY() : new BigDecimal("0"));
                        trailVo.setCoordinate_z(rydwRyjzPo.getCoordinateZ() != null ? rydwRyjzPo.getCoordinateZ() : new BigDecimal("0"));
                    } else {
                        trailVo.setSubstationName(stationCode);
                        trailVo.setCoordinate_x(new BigDecimal("0"));
                        trailVo.setCoordinate_y(new BigDecimal("0"));
                        trailVo.setCoordinate_z(new BigDecimal("0"));
                    }
                    list.add(trailVo);
                }
            }

            return list;
        } else {
            String sql = "SELECT " +
                    "	t.station_code as substationCode, " +
                    "	t.station_in_time AS inTime, " +
                    "	t1.coordinate_x, " +
                    "	t1.coordinate_z, " +
                    "	t1.local_notes as substation_name " +
                    "FROM " +
                    "	( SELECT station_code, station_in_time FROM \"dwd_jxry_now_personnelinfo\" WHERE staff_code = '%s' AND in_time = '%s' %s GROUP BY station_code, station_in_time ) T " +
                    "JOIN \"dwd_jxry_base_stationinfo\" t1 on ( T.station_code = t1.substation_code )  " +
                    "ORDER BY " +
                    "	T.station_in_time";
            if (StringUtils.isNotBlank(mineCode)) {
                mineCode = " and mine_code='" + mineCode + "'";
            } else {
                mineCode = "";
            }
            List<TrailVo> query = locationJdbcTemplate.query(String.format(sql, staffCode, inTime, mineCode), new Object[]{}, new BeanPropertyRowMapper<>(TrailVo.class));

            String lastSubstationName = "";//最后分站名称，避免重复
            for (TrailVo trailVo : query) {
                if (!lastSubstationName.equals(trailVo.getSubstationName())) {
                    list.add(trailVo);
    //                lastSubstationName=trailVo.getSubstationName();
                }
            }
            return list;
        }
    }

    @Override
    public List<TrailVo> hisTrail(String staffCode, String inTime, String mineCode) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date parse = sdf.parse(inTime);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(parse);
        calendar.set(Calendar.DAY_OF_MONTH, calendar.get(Calendar.DAY_OF_MONTH) + 1);
        String end = sdf.format(calendar.getTime());
        calendar.set(Calendar.DAY_OF_MONTH, calendar.get(Calendar.DAY_OF_MONTH) - 2);
        String start = sdf.format(calendar.getTime());
        String sql = "SELECT *,station_code as substationCode,substation_location as substationName  FROM \"dwd_jxry_his_personnelinfo\" where in_time ='%s' and staff_code='%s' " +
                "and data_time>'%s' and data_time<'%s' %s ";
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        List<TrailVo> query = locationJdbcTemplate.query(String.format(sql, inTime, staffCode, start, end, mineCode), new Object[]{}, new BeanPropertyRowMapper<>(TrailVo.class));
        List<TrailVo> list = new ArrayList<>();
        String lastSubstationName = "";//最后分站名称，避免重复
        for (TrailVo trailVo : query) {
            if (!lastSubstationName.equals(trailVo.getSubstationName())) {
                list.add(trailVo);
            }
        }
        //去除重复数据
        List<TrailVo> collect = list.stream()
                .collect(Collectors.toMap(
                        vo -> vo.getSubstationCode() + "-" + vo.getSubstationName(),
                        vo -> vo,
                        (existing, replacement) -> existing
                ))
                .values()
                .stream()
                .sorted(Comparator.comparing(TrailVo::getInTime)) // 根据 inTime 字段升序排序
                .collect(Collectors.toList());
        String finalMineCode = mineCode;
        collect.forEach(s -> {
            //查询分站坐标
            try {
                LambdaQueryWrapper<JxryBaseStationInfoPo> queryWrapper = new LambdaQueryWrapper<>();
                queryWrapper.eq(JxryBaseStationInfoPo::getSubstationCode, s.getSubstationCode());
                queryWrapper.eq(StringUtils.isNotBlank(finalMineCode), JxryBaseStationInfoPo::getMineCode, finalMineCode);
                JxryBaseStationInfoPo jxryBaseStationInfoPo = jxryBaseStationInfoMapper.selectOne(queryWrapper);
                if (jxryBaseStationInfoPo != null) {
                    s.setCoordinate_x(jxryBaseStationInfoPo.getCoordinateX());
                    s.setCoordinate_y(jxryBaseStationInfoPo.getCoordinateY());
                    s.setCoordinate_z(jxryBaseStationInfoPo.getCoordinateZ());
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        });
        return collect;
    }

    @Override
    public List<PeopleVo> peopleInfoByArea(String areaCode, String stationCode, String mineCode) {
        String sql = "SELECT t1.staff_code as userCoed,t1.staff_name as name,in_time as intoTime,t1.station_in_time as stationInTime,t1.mine_leader as leader," +
                "COALESCE(t2.dept_name,'无') as dept,COALESCE(t2.duty,'无')  as duty,t1.working_condition as status,t1.station_code as stationCode from dwd_jxry_now_personnelinfo t1 left join dwd_jxry_base_personnelinfo t2 on (t1.staff_code=t2.staff_code) " +
                "where t1.data_time = ( SELECT MAX ( data_time ) FROM dwd_jxry_now_personnelinfo %s ) ";

        if (StringUtils.isNotBlank(areaCode)) {
            sql = sql + " and t1.area_code='" + areaCode + "'";
        }
        if (StringUtils.isNotBlank(stationCode)) {
            sql = sql + " and t1.station_code='" + stationCode + "'";
        }
        if (StringUtils.isNotBlank(mineCode)) {
            sql = sql + " and t1.mine_code='" + mineCode + "' ";
            mineCode = " where mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        String format = String.format(sql, mineCode);
        List<PeopleVo> peopleVos = locationJdbcTemplate.query(format, new BeanPropertyRowMapper<>(PeopleVo.class));
        return peopleVos;
    }

    @Override
    public IndexTodayVo IndexTodayInfo(String mineCode) throws ParseException {
        IndexTodayVo todayVo = new IndexTodayVo();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date parse = null;
        if ("150621011038".equals(mineCode)) {
            parse = sdf.parse("2020-05-08");
            try {
                Map<String, String> stringStringMap = ftpUtile(mineCode);
                todayVo.setDuty(stringStringMap.get("duty"));
                todayVo.setLead(stringStringMap.get("lead"));
            } catch (Exception e) {
                e.printStackTrace();
                log.error("未查询到带班人:" + e.getMessage());
            }
        } else if ("620824009734".equals(mineCode)) {
            parse = sdf.parse("2016-03-27");
            try {
                Map<String, String> stringStringMap = ftpUtile(mineCode);
                todayVo.setDuty(stringStringMap.get("duty"));
                todayVo.setLead(stringStringMap.get("lead"));
            } catch (Exception e) {
                e.printStackTrace();
                log.error("未查询到带班人:" + e.getMessage());
            }
        } else if ("150781013603".equals(mineCode)) {
            parse = sdf.parse("2021-12-11");
            R<OnDutyVo> onDuty = remoteOnDutyService.getOnDuty(null, mineCode);
            if (onDuty.getCode() == 200) {
                todayVo.setLead(onDuty.getData().getLeadPersonNames());
                todayVo.setDuty(onDuty.getData().getDutyPersonNames());
            }
        } else if ("150781013670".equals(mineCode)) {
            parse = sdf.parse("2021-12-11");
            R<OnDutyVo> onDuty = remoteOnDutyService.getOnDuty(null, mineCode);
            if (onDuty.getCode() == 200) {
                todayVo.setLead(onDuty.getData().getLeadPersonNames());
                todayVo.setDuty(onDuty.getData().getDutyPersonNames());
            }
        } else if ("150781013194".equals(mineCode)) {
            parse = sdf.parse("2021-12-11");
            R<OnDutyVo> onDuty = remoteOnDutyService.getOnDuty(null, mineCode);
            if (onDuty.getCode() == 200) {
                todayVo.setLead(onDuty.getData().getLeadPersonNames());
                todayVo.setDuty(onDuty.getData().getDutyPersonNames());
            }
        } else if ("150781013671".equals(mineCode)) {
            //灵露煤矿
            //安全生产天数开始时间
            parse = sdf.parse("2020-7-16");
            R<OnDutyVo> onDuty = remoteOnDutyService.getOnDuty(null, mineCode);
            if (onDuty.getCode() == 200) {
                todayVo.setLead(onDuty.getData().getLeadPersonNames());
                todayVo.setDuty(onDuty.getData().getDutyPersonNames());
            }
        } else {
            //默认安全天数开始时间 防止未知天数null错误
            parse = new Date();
        }
        long safetyDays = (new Date().getTime() - parse.getTime()) / 24 / 60 / 60 / 1000;
        todayVo.setSafetyDays(Integer.parseInt(safetyDays + ""));
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        String countSql = "select count(1) from dwd_jxry_now_personnelinfo" +
                " where flag_bit='1' and data_time > CURRENT_DATE and data_time = " +
                "( SELECT MAX (data_time ) FROM \"dwd_jxry_now_personnelinfo\" where 1=1 %s ) %s";
        try {
            Integer count = locationJdbcTemplate.queryForObject(String.format(countSql, mineCode, mineCode), Integer.class);
            todayVo.setUnderNumber(count);
        } catch (EmptyResultDataAccessException e) {
            log.error("未查询到井下人数");
        }
//        SimpleDateFormat sdf1 = new SimpleDateFormat("HH");
//        Date date = new Date();
//        int hours = Integer.parseInt(sdf1.format(date));
//        String day = sdf.format(date);
//        StringBuilder where = new StringBuilder(" ");
//        if (hours >= 6 && hours < 14) {
//            where.append(" and in_time >='").append(day).append(" 05:00:00'");
//            where.append(" and in_time <='").append(day).append(" 13:00:00'");
//        } else if (hours >= 14 && hours < 21) {
//            where.append(" and in_time >='").append(day).append(" 13:00:00'");
//            where.append(" and in_time <='").append(day).append(" 20:00:00'");
//        } else {
//            Calendar calendar = Calendar.getInstance();
//            calendar.setTime(date);
//            calendar.set(Calendar.DAY_OF_MONTH, calendar.get(Calendar.DAY_OF_MONTH) + 1);
//            String day1 = sdf.format(calendar.getTime());
//            where.append(" and in_time >='").append(day).append(" 20:00:00'");
//            where.append(" and in_time <='").append(day1).append(" 05:00:00'");
//        }
//        StringBuilder sql = new StringBuilder("select count(distinct staff_code) from dwd_jxry_now_personnelinfo where flag_bit='1' %s ");
//                "and data_time = ( SELECT MAX (data_time ) FROM \"dwd_jxry_now_personnelinfo\" ) ");
//        sql.append(where);
//        try {
//            Integer count = locationJdbcTemplate.queryForObject(String.format(sql.toString(), mineCode), Integer.class);
//            todayVo.setDutyNumber(count);
//        } catch (EmptyResultDataAccessException e) {
//            log.error("未查询到当班下井人数");
//        }
        return todayVo;
    }


    @Override
    public ResponsePage<AlarmVo> getAlarmPageList(String alarmType, String beginTime, String endTime, int page, int pageSize, String mineCode) {
        StringBuilder sb = new StringBuilder("");
        StringBuilder where = new StringBuilder();
        if (StringUtils.isNotBlank(beginTime)) {
            where.append(" and in_station_time >='").append(beginTime).append("' ");
        }
        if (StringUtils.isNotBlank(endTime)) {
            where.append(" and in_station_time <='").append(endTime).append("' ");
        }
        if (StringUtils.isNotBlank(mineCode)) {
            where.append(" and mine_code='" + mineCode + "'");
        }
        switch (alarmType) {
            case "":
            case "限制区域报警":
                sb.append(" SELECT null as areaName,current_area_code AS areaCode, staff_code AS staffCode, staff_name as staffName, alarm_start_time AS beginTime\n" +
                        ",alarm_end_time AS endTime,'限制区域报警' as alarmType,in_hole_time as entryTime," +
                        "current_station_code as stationCode,in_area_time as inAreaTime, in_station_time as inStationTime," +
                        "0 as specifyNumber,0 as currentNumber,null as areaStaff  FROM \"dwd_jxry_base_accessrestrictedarea\" where 1=1 ").append(where);
                if ("".equals(alarmType)) {
                    sb.append(" UNION all ");
                } else {
                    break;
                }
            case "人员求救":
                sb.append(" SELECT null as areaName,current_area_code AS areaCode, staff_code AS staffCode, staff_name as staffName, cry_start_time AS beginTime\n" +
                        ",cry_end_time AS endTime,'人员求救' as alarmType,in_hole_time as entryTime,current_station_code as stationCode,in_area_time as inAreaTime, in_station_time as inStationTime\n" +
                        ",0 as specifyNumber,0 as currentNumber,null as areaStaff \n" +
                        "from dwd_jxry_base_cryinfo where 1=1 ").append(where);
                if ("".equals(alarmType)) {
                    sb.append(" UNION all ");
                } else {
                    break;
                }
            case "人员超时":
                sb.append(" SELECT null as areaName,area_code AS areaCode, staff_code AS staffCode, staff_name as staffName, alarm_start_time AS beginTime\n" +
                        ",alarm_end_time AS endTime,'人员超时' as alarmType,in_hole_time as entryTime,station_code as stationCode,in_area_time as inAreaTime, in_station_time as inStationTime\n" +
                        ",0 as specifyNumber,0 as currentNumber,null as areaStaff \n" +
                        "from dwd_jxry_base_timeoutalarm where 1=1 ").append(where);
                if ("".equals(alarmType)) {
                    sb.append(" UNION all ");
                } else {
                    break;
                }
            case "超限报警":
                sb.append(" SELECT area_name as areaName,area_code AS areaCode,'' AS staffCode,'' as staffName," +
                        "alarm_start_time AS beginTime,alarm_end_time AS endTime,'超限报警' as alarmType, " +
                        "null as entryTime,area_code as stationCode,null as inAreaTime,in_station_time as" +
                        " inStationTime,specify_number as specifyNumber,current_number as currentNumber," +
                        "area_staff as areaStaff from dwd_jxry_base_overmanalarm where 1=1 ").append(where);
        }
        StringBuilder sqlCount = new StringBuilder(" select count(1) from (").append(sb).append(") t");
        Integer count = locationJdbcTemplate.queryForObject(sqlCount.toString(), Integer.class);
        if (count > 0) {
            StringBuilder sql = new StringBuilder(" select * from (").append(sb).append(") t ")
                    .append(" order by inStationTime desc ")
                    .append(" limit ").append(pageSize).append(" offset ").append(page * pageSize);
            List<AlarmVo> list = locationJdbcTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(AlarmVo.class));
            return new ResponsePage<AlarmVo>(list, count);
        } else {
            return new ResponsePage<AlarmVo>(new ArrayList(), count);
        }
    }

    @Override
    public ResponsePage<JxryNowPersonnelinfoEntity> getPageDate(String duty, String dept, String staffName, String leader, Pageable pageable, String mineCode) {
        Page<JxryNowPersonnelinfoEntity> pageDate = jxryNowPersonnelinfoRepository.getPageDate(duty == null ? "" : duty,
                dept == null ? "" : dept,
                staffName == null ? "" : staffName,
                leader == null ? "" : leader,
                pageable, mineCode == null ? "" : mineCode);
        List<JxryNowPersonnelinfoEntity> content = pageDate.getContent();
        content.forEach(s -> {
            String substationLocation = s.getSubstationLocation();
            if (StringUtils.isEmpty(substationLocation)) {
                String stationCode = s.getStationCode();
                //去分站数据表查询
                DwdJxryBaseStationInfoPo dwdJxryBaseStationInfoPo = dwdJxryBaseStationInfoMapper.selectOne(new LambdaQueryWrapper<DwdJxryBaseStationInfoPo>()
                        .eq(DwdJxryBaseStationInfoPo::getSubstationCode, stationCode).last("LIMIT 1"));
                if (dwdJxryBaseStationInfoPo != null) {
                    s.setSubstationLocation(dwdJxryBaseStationInfoPo.getSubstationName());
                } else {
                    s.setSubstationLocation(stationCode);
                }
            }
        });

        return new ResponsePage<>(content, pageDate.getTotalElements());
    }

    @Override
    public ResponsePage<JxryHisPersonnelinfoEntity> getPageDate(String duty, String dept, String staffName, String leader, String beginTime, String endTime, Pageable pageable, String mineCode) {
        Page<JxryHisPersonnelinfoEntity> pageDate = jxryHisPersonnelinfoRepository.getPageDate(duty == null ? "" : duty,
                dept == null ? "" : dept,
                staffName == null ? "" : staffName,
                leader == null ? "" : leader,
                beginTime == null ? "" : beginTime,
                endTime == null ? "" : endTime,
                pageable,
                mineCode == null ? "" : mineCode);
        if (pageDate.getContent().size() > 0) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Map<String, String> allStation = getAllStation();
            pageDate.getContent().forEach(s -> {
                //6208240097340000000069&2022-08-30 20:41:56,6208240097340000000070&2022-08-30 20:46:03,6208240097340000000007&2022-08-30 20:54:07,6208240097340000000008&2022-08-30 20:55:05,6208240097340000000012&2022-08-30 21:11:30,6208240097340000000013&2022-08-30 21:12:07,6208240097340000000015&2022-08-30 21:13:36
                String way = s.getPeopleWay();
                if (StringUtils.isBlank(way)) {
                    way="";
                }
                String[] splits = way.split(",");
                if (splits.length > 0) {
                    List<TrailVo> trailVos = new ArrayList<>();
                    for (int i = 0; i < splits.length; i++) {
                        String[] split = splits[i].split("&");
                        if (split.length > 1) {
                            TrailVo trailVo = new TrailVo();
                            trailVo.setSubstationCode(split[0]);
                            trailVo.setSubstationName(allStation.get(split[0]));
                            try {
                                trailVo.setInTime(sdf.parse(split[1]));
                            } catch (ParseException e) {
                                e.printStackTrace();
                            }
                            trailVos.add(trailVo);
                        }
                    }
                    s.setTrailVo(trailVos);
                }
                String substationLocation = s.getSubstationLocation();
                if (StringUtils.isEmpty(substationLocation)) {
                    String stationCode = s.getStationCode();
                    //去分站数据表查询
                    DwdJxryBaseStationInfoPo dwdJxryBaseStationInfoPo = dwdJxryBaseStationInfoMapper.selectOne(new LambdaQueryWrapper<DwdJxryBaseStationInfoPo>()
                            .eq(DwdJxryBaseStationInfoPo::getSubstationCode, stationCode).last("LIMIT 1"));
                    if (dwdJxryBaseStationInfoPo != null) {
                        s.setSubstationLocation(dwdJxryBaseStationInfoPo.getSubstationName());
                    } else {
                        s.setSubstationLocation(stationCode);
                    }
                }

            });
        }
        return new ResponsePage<>(pageDate.getContent(), pageDate.getTotalElements());
    }

    @Override
    public List<String> getAllDeptOrDuty(String type, String mineCode) {
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = "  mine_code='" + mineCode + "' and";
        }
        String sql = "SELECT %s FROM \"dwd_jxry_base_personnelinfo\" where %s %s is not null GROUP BY %s";
        return locationJdbcTemplate.queryForList(String.format(sql, type, mineCode, type, type), String.class);
    }

    @Override
    public List<String> getUserIds(List<String> stations, String mineCode) {
        String sql = "SELECT id_number FROM dwd_jxry_base_personnelinfo t1 join dwd_jxry_now_personnelinfo t2  on (t1.staff_code=t2.staff_code)" +
                " where t2.data_time=( SELECT MAX (data_time ) FROM \"dwd_jxry_now_personnelinfo\" where 1=1 % ) and t2.station_code in ('%s') %s";
        String where = "";
        if (StringUtils.isNotBlank(mineCode)) {
            mineCode = " and t2.mine_code='" + mineCode + "'";
            where = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }
        List<String> strings = locationJdbcTemplate.queryForList(String.format(sql, where, StringUtils.join(stations, "','"), mineCode), String.class)
                .stream().filter(s -> !"".equals(s)).collect(Collectors.toList());
        return remoteUserService.getUserListByIdCardList(strings).stream().filter(s -> !"".equals(s)).collect(Collectors.toList());
    }

    public Map<String, String> getAllStation() {
        String sql = "SELECT substation_code,local_notes FROM \"dwd_jxry_base_stationinfo\"";
        return locationJdbcTemplate.queryForList(sql).stream().collect(Collectors.toMap(s -> "" + s.get("substation_code"), s -> "" + s.get("local_notes"), (s, s2) -> s2));
    }

    public List<Map<String, Object>> getStationAddr() {
        String sql = "SELECT coordinate_x,coordinate_y,coordinate_z,local_notes,substation_code FROM \"dwd_jxry_base_stationinfo\"";
        String sqlCount = "SELECT station_code,count(1), string_agg(staff_name,',') as names FROM \"dwd_jxry_now_personnelinfo\" where  data_time=( SELECT MAX (data_time ) FROM \"dwd_jxry_now_personnelinfo\" ) GROUP BY station_code";
        Map<Object, Object> collect = locationJdbcTemplate.queryForList(sqlCount).stream().collect(Collectors.toMap(s -> s.get("station_code"), s -> new HashMap() {{
            put("count", s.get("count"));
            put("names", s.get("names"));
        }}));
        ;
        List<Map<String, Object>> maps = locationJdbcTemplate.queryForList(sql);
        maps.forEach(s -> {
            if (collect.get(s.get("substation_code")) != null) {
                s.put("personNum", collect.get(s.get("substation_code")));
            } else {
                s.put("personNum", new HashMap() {{
                    put("count", 0);
                    put("names", "");
                }});
            }
        });
        return maps;
    }

    public List<Map<String, Object>> getNewStationAddr(String mineCode) {

        String sql = "SELECT coordinate_x,coordinate_y,coordinate_z,local_notes,substation_code FROM \"dwd_jxry_base_stationinfo\"";
        String sqlCount = "SELECT station_code,count(1), string_agg(staff_name,',') as names,max(area_code) as area_code  FROM \"dwd_jxry_now_personnelinfo\" where  data_time=( SELECT MAX (data_time ) FROM \"dwd_jxry_now_personnelinfo\" where 1=1 %s ) %s GROUP BY station_code";
        String mineCodess = mineCode;
        if (StringUtils.isNotBlank(mineCode)) {
            sql = sql + " where mine_code='" + mineCode + "'";
//            where = " and t2.mine_code='" + mineCode + "'";
            mineCode = " and mine_code='" + mineCode + "'";
        } else {
            mineCode = "";
        }

        Map<Object, Object> collect = locationJdbcTemplate.queryForList(String.format(sqlCount, mineCode, mineCode)).stream().collect(Collectors.toMap(s -> s.get("station_code").toString().trim(), s -> new HashMap() {{
            put("count", s.get("count"));
            put("names", s.get("names"));
            put("areaCode", s.get("area_code"));
        }}));
        List<Map<String, Object>> list = locationJdbcTemplate.queryForList(sql);
        List<Map<String, Object>> maps = new ArrayList<>();
        //250203工作面传感器合并人数查询
        for (Map<String, Object> s : list) {
            if (s.get("local_notes") == null) {
                continue;
            } else {
                maps.add(s);
            }
        }

        Map<String, List<PeopleVo>> peopleMaps = new HashMap<>();
        List<PeopleVo> peopleVos = peopleInfoByArea(null, null, mineCodess);
        peopleVos.forEach(s -> {
            if (peopleMaps.get(s.getStationCode().trim()) == null) {
                List<PeopleVo> list1 = new ArrayList<>();
                list1.add(s);
                peopleMaps.put(s.getStationCode().trim(), list1);
            } else {
                peopleMaps.get(s.getStationCode().trim()).add(s);
            }
        });
        maps.forEach(s -> {
            if (peopleMaps.get(s.get("substation_code")) != null) {
                s.put("details", peopleMaps.get(s.get("substation_code")));
            }
            if (collect.get(s.get("substation_code")) != null) {
                s.put("personNum", collect.get(s.get("substation_code")));
            } else {
                s.put("personNum", new HashMap() {{
                    put("count", 0);
                    put("names", "");
                }});
            }
        });
        return maps.stream().sorted(Comparator.comparingInt((Map<String, Object> o) ->
                Integer.parseInt(((HashMap) o.get("personNum")).get("count") + "")
        ).reversed()).collect(Collectors.toList());
    }

    public Map<String, String> ftpUtile(String mineCode) throws Exception {
        Map map = new HashMap();
        map.put("duty", "");
        map.put("lead", "");
        if ("meiye".equals(profile) || "beineng".equals(profile)) {//煤业的查表
            DutyLeadEntity firstByMineCodeOrderByDataTimeDesc = dutyLeadEntityRepository.findFirstByMineCodeOrderByDataTimeDesc(mineCode);

            if (firstByMineCodeOrderByDataTimeDesc != null) {
                map.put("duty", firstByMineCodeOrderByDataTimeDesc.getDuty());
                map.put("lead", firstByMineCodeOrderByDataTimeDesc.getLead());
            }
            return map;
        }
//      略过高头窑的缓存
        if (!"150621011038".equals(mineCode)) {
            //煤矿不是高头窑,则去读取缓存,如果是高头窑则跳过缓存
            Map cacheMap = RedisUtils.getCacheObject(mineCode + "带值班");
            if (cacheMap != null && !cacheMap.isEmpty()) {
                return cacheMap;
            }
        }
        if ("620824009734".equals(mineCode)) {//砚北
            map = yanbeiFtp();
            return map;
        } else if ("150621011038".equals(mineCode)) {//高头窑
            //2023-06-02 三楼led 改为一楼led
//            map = gaotouyaoFtp();
            //20230608 新增需求，判断当前时间是否是凌晨至早上八点，
            //如果是 则读取数据库带值班 今天的第一行数据，并判断返回值是否为空
            //如果否 则直接读取httpRtf

            //获取小时
            SimpleDateFormat sdf1 = new SimpleDateFormat("HH");
            SimpleDateFormat sdfMinute = new SimpleDateFormat("mm");
            Date date = new Date();
            //获取现在的时间 小时数
            int hours = Integer.parseInt(sdf1.format(date));
            //获取分钟数
            int minutes = Integer.parseInt(sdfMinute.format(date));

            //24点-八点是夜班
            //20231114 将时间改成23：30-7：30
            if ((hours == 23 && minutes >= 30) || (hours >= 0 && hours < 7) || (hours == 7 && minutes < 30)) {
                //读取表中得数据
                map = gaotouyaoTable();
            } else {
                map = gaotouyaoHttpRTF();
            }

        }
        RedisUtils.setCacheObject(mineCode + "带值班", map, Duration.ofMinutes(1l));

        return map;
    }

    /**
     * 读取高头窑的带值班表 获取表中当前中的最新的一个记录
     *
     * @return
     */
    private Map gaotouyaoTable() {
        Map map = new HashMap();
        map.put("duty", "");
        map.put("lead", "");

        DutyLeadEntity findOneEntity = dutyLeadEntityRepository.findOneDay();
        if (findOneEntity == null) {
            //查不到当天的记录  udqu
            map = gaotouyaoHttpRTF();
            return map;
        }
        map.put("duty", findOneEntity.getDuty());
        map.put("lead", findOneEntity.getLead());
        RedisUtils.setCacheObject("150621011038带值班-正确结果", map, Duration.ofDays(7));
        return map;
    }

    @Override
    public List<DataStateVo> getMaxTime(String mineCode, String companyCode) {
        String sql = "SELECT mine_code as mineCode,max(data_time) as maxTime  FROM dwd_jxry_now_personnelinfo where 1=1 %s %s group by mine_code";
        if (StringUtils.isNotBlank(mineCode)) {
            sql = sql.replaceFirst("mine_code", "'" + mineCode + "'");
            sql = sql.replace("group by mine_code", "");
            mineCode = "and mine_code='" + mineCode + "' ";

        } else {
            mineCode = "";
        }
        if (StringUtils.isNotBlank(companyCode) && StringUtils.isBlank(mineCode)) {
            companyCode = "and company_code='" + companyCode + "' ";
        } else {
            companyCode = "";
        }
        List<DataStateVo> query = locationJdbcTemplate.query(String.format(sql, mineCode, companyCode), new BeanPropertyRowMapper<>(DataStateVo.class));
        return query;
    }

    /**
     * 获取井下人员BaseSensorTable表所有信息
     *
     * @return
     */
    @Override
    public List<BaseSensorCoordDto> getBaseSensorTableAllList(String mineCode) {
        //查询所有的分站信息
        LambdaQueryWrapper<JxryBaseStationInfoPo> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(StringUtils.isNotBlank(mineCode), JxryBaseStationInfoPo::getMineCode, mineCode);
        List<JxryBaseStationInfoPo> jxryBaseStationInfoPos = jxryBaseStationInfoMapper.selectList(lambdaQueryWrapper);
        //类型转换
        if (jxryBaseStationInfoPos.isEmpty()) {
            return new ArrayList<>();
        }
        return jxryBaseStationInfoPos.stream().map(t -> {
            BaseSensorCoordDto baseSensorCoordDto = new BaseSensorCoordDto();
            //传感器编码 分站编码
            baseSensorCoordDto.setPointCode(t.getSubstationCode());
            //传感器名称 分站名称
            baseSensorCoordDto.setLocation(t.getLocalNotes());
            //传感器类型编码
            baseSensorCoordDto.setSensorTypeCode("4001");
            //传感器类型名称
            baseSensorCoordDto.setSensorTypeName(t.getType());
            //x
            baseSensorCoordDto.setXCoordinate(t.getCoordinateX());
            //y
            baseSensorCoordDto.setYCoordinate(t.getCoordinateY());
            //z
            baseSensorCoordDto.setZCoordinate(t.getCoordinateZ());
            return baseSensorCoordDto;
        }).filter(dto -> dto.getLocation() != null)
                .sorted(Comparator.comparing(BaseSensorCoordDto::getLocation))
                .collect(Collectors.toList());
    }

    @Override
    public Integer getPitNumber(String mineCode) {
        String countSql = "SELECT COUNT(1)\n" +
                "FROM dwd_jxry_now_personnelinfo\n" +
                "WHERE flag_bit = '1'\n" +
                "AND data_time > CURRENT_DATE\n" +
                "AND mine_code = '%s' \n" +
                "AND data_time = (SELECT MAX(data_time) FROM dwd_jxry_now_personnelinfo)";
        Integer count = locationJdbcTemplate.queryForObject(String.format(countSql, mineCode), Integer.class);
        return count;
    }

    private Map yanbeiFtp() throws Exception {
        Map<String, String> map = new HashMap<>();
        FTPClient ftpClient = new FTPClient();//import org.apache.commons.net.ftp.FTPClient;

        ftpClient.connect("172.17.30.140");//连接ftp
        ftpClient.login("hn", "hn@2024");//登陆ftp


        if (FTPReply.isPositiveCompletion(ftpClient.getReplyCode()))//是否连接成功,成功true,失败false
        {
            ftpClient.enterLocalPassiveMode();
            FTPFile[] ftpFiles = ftpClient.listFiles();
//            if("620824009734".equals(mineCode)) {//砚北
            for (FTPFile file : ftpFiles) {
                byte[] gbks = file.getName().getBytes("GBK");
                String name = new String(gbks, "ISO-8859-1");
//                log.error(name);
//                if (name.endsWith("1_0.txtf")) {
//                    File localFile = new File("/home/hn/文本1_0.txtf");
//                    OutputStream is = new FileOutputStream(localFile);
//                    ftpClient.retrieveFile(file.getName(), is);
//                    is.close();
//                    map.put("duty", getFlieStr("/home/hn/文本1_0.txtf"));
//                }
                if (name.endsWith("1_2.txtf")) {
                    File localFile = new File("/home/hn/文本1_2.txtf");
                    OutputStream is = new FileOutputStream(localFile);
                    ftpClient.retrieveFile(file.getName(), is);
                    is.close();
                    return getFlieStr("/home/hn/文本1_2.txtf");
                }
            }

        }
        ftpClient.disconnect();
        return map;
    }

    private Map getFlieStr(String local) throws Exception {
        FileInputStream fis = new FileInputStream(local);
//        if (local.endsWith("1_0.txtf")) {
        if (!local.contains("1_0")) {
            byte[] head = new byte[3];
            fis.read(head);
        }
        Map map = new HashMap();
//        }
        BufferedInputStream bis = new BufferedInputStream(fis, 1024);
        ByteArrayOutputStream bos = new ByteArrayOutputStream(1024);
        byte[] cache = new byte[10240];
        int length = 0;
        while ((length = bis.read(cache)) != -1) {
            bos.write(cache, 0, length);
        }
        String utf_16LE = new String(cache, "UTF_16LE");//1_0的文件可以
//        System.out.println(utf_16LE);
        if (bos != null) {
            bos.close();
        }
        if (bis != null) {
            bis.close();
        }
        if (fis != null) {
            fis.close();
        }
        log.info(utf_16LE);
//        Pattern pat = Pattern.compile("([：][\\u4e00-\\u9fa5| ]{1,4}+)");
//        Matcher mat = pat.matcher(utf_16LE);
//        String str = "";
//        if (mat.find()) {
//            str = mat.group(0).replace("：", "");
//        } else {
//            Pattern pat2 = Pattern.compile("([ਰ][\\u4e00-\\u9fa5| ]{1,4}+)");
//            Matcher mat2 = pat2.matcher(utf_16LE);
//            if (mat2.find()) {
//                str = mat2.group(0).replace("ਰ", "");
//            }
//        }
        int strStartIndex = utf_16LE.indexOf("值班矿领导：");
        int strEndIndex = utf_16LE.indexOf("带班矿领导：");
        if (strStartIndex < 0) {
            map.put("duty", "");
        }
        if (strEndIndex < 0) {
            map.put("duty", "");
            map.put("lead", "");
        }
        String result = utf_16LE.substring(strStartIndex, strEndIndex).substring("值班矿领导：".length());
        map.put("duty", result.trim());
        String result1 = utf_16LE.substring(strEndIndex, utf_16LE.length()).substring("带班矿领导：".length());
        map.put("lead", result1.trim());
        return map;
    }

    public Map gaotouyaoHttpRTF() {

//        String url = "http://10.33.171.88:80/值带班.rtf";
        String url = "http://192.168.4.200:1222/值带班.rtf";

        //通过URL获取输入流
//        InputStream inputStreamByUrl = getInputStreamByUrl(url);


        InputStream inputStreamByUrl = null;
        try {
            URL url1 = new URL(url);
            inputStreamByUrl = url1.openStream();
        } catch (MalformedURLException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        String result = null;
        //20230621  改成通过第三方库tika 读取rtf文件   用于解决生僻字问题,以及未涵盖的编码字库
//        try {
//            DefaultStyledDocument styledDoc = new DefaultStyledDocument();
//            new RTFEditorKit().read(inputStreamByUrl, styledDoc, 0);
//            result = new String(styledDoc.getText(0,styledDoc.getLength()).getBytes("ISO8859_1"),"GBK");
//            //注意这边加上GBK即可；
//        } catch (IOException e) {
//            e.printStackTrace();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }

        Tika tika = new Tika();
        try {

            result = tika.parseToString(inputStreamByUrl);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (TikaException e) {
            throw new RuntimeException(e);
        }
        //字符串解析结果result =
        //值班领导：司伟东  周德梁  邢建栋
        //早班带班：赵忠理  乔  治
        //中班带班：于西广  汪兴斌
        //夜班带班：魏金龙  赵  斌
        //3-1202工作面搬家倒面专职跟班：
        //早班跟班：赵忠理  刘英华
        //中班跟班：于西广  于  波
        //夜班跟班：魏金龙  佟  凯
        //年度原煤产量计划10Mt
        //年度掘进计划23964m
        //六月份原煤产量计划720000t
        //六月份掘进计划1853m
        //昨日产量10934t    月累计10934t    年累计4733885t
        //昨日销售31058t    月累计31058t   年累计4261900t      昨日掘进38m     月累计38m    年累计9139m
//        System.out.println("result = " + result);
        //封装返回结果
        Map map = new HashMap();
        map.put("duty", "");
        map.put("lead", "");
        //根据换行符分割
//        String lines[] = result.split("\r?\n");
        //line = 值班领导：司伟东  周德梁  邢建栋
        //line = 早班带班：赵忠理  乔  治
        //line = 中班带班：于西广  汪兴斌
        //line = 夜班带班：魏金龙  赵  斌
        //line = 3-1202工作面搬家倒面专职跟班：
        //line = 早班跟班：赵忠理  刘英华
        //line = 中班跟班：于西广  于  波
        //line = 夜班跟班：魏金龙  佟  凯
        //line = 年度原煤产量计划10Mt
        //line = 年度掘进计划23964m
        //line = 六月份原煤产量计划720000t
        //line = 六月份掘进计划1853m
        //line = 昨日产量10934t    月累计10934t    年累计4733885t
        //line = 昨日销售31058t    月累计31058t   年累计4261900t      昨日掘进38m     月累计38m    年累计9139m
        //值班领导
        String dutystring = "";
        if (result.contains("值班领导")) {
            dutystring = result.substring(result.indexOf("值班领导"), result.indexOf("早班带班"));
        } else if (result.contains("地面带班")) {
            dutystring = result.substring(result.indexOf("地面带班"), result.indexOf("早班带班"));
        } else if (result.contains("地面值带班")) {
            dutystring = result.substring(result.indexOf("地面值带班"), result.indexOf("早班带班"));
        }
        //早班领导
        String morningString = "";
        morningString = result.substring(result.indexOf("早班"), result.indexOf("中班"));
        //中班领导
        String middleString = "";
        middleString = result.substring(result.indexOf("中班"), result.indexOf("夜班"));
        //晚班领导
        String nightString = "";
        nightString = result.substring(result.indexOf("夜班"), result.indexOf("年"));
        List<String> lines = new ArrayList<>();
        lines.add(0, dutystring.trim());
        lines.add(1, morningString.trim());
        lines.add(2, middleString.trim());
        lines.add(3, nightString.trim());
        for (String line : lines) {
            //三种情况
            //1.周一至周五 值班领导
            //2.周六至周天 地面带班
            //3.节假日   地面值带班

            //首先判断今天是否是周六日
            //如果今天是周六日，则值班领导改为  地面带班

            Calendar instance = Calendar.getInstance();
            // 获取今天星期几
            int i = instance.get(Calendar.DAY_OF_WEEK);
            // 获取周六日
            int saturday = Calendar.SATURDAY;
            int sunday = Calendar.SUNDAY;
            // 判断今天是否是周六日
            if (i == saturday || i == sunday) {
                //是周六日  读取 地面带班
                //判断当前行是否带有 地面带班
                if (line.contains("地面带班") || line.contains("地面值带班") || line.contains("值班领导")) {
                    String[] split = line.split("：");
                    //获取数值的组后一个
                    String s = split[1].trim();
                    map.put("duty", s);
                }
            } else {
                //周一 至周五  读取  值班领导
                //判断当前行是否带有 值班领导
                if (line.contains("地面带班") || line.contains("值班领导") || line.contains("地面值带班")) {
                    String[] split = line.split("：");
                    //获取数值的组后一个
                    String s = split[1].trim();
                    map.put("duty", s);
                }
            }

            //获取小时
            SimpleDateFormat sdf1 = new SimpleDateFormat("HH");
            SimpleDateFormat sdfMinute = new SimpleDateFormat("mm");
            Date date = new Date();
            //获取现在的时间
            int hours = Integer.parseInt(sdf1.format(date));
            //获取分钟数
            int minutes = Integer.parseInt(sdfMinute.format(date));


            //八点-16点是早班时间，
            //16点-24点是中班，
            //24点-八点是夜班

            //20231114 改为
            //7:30点-15:30点是早班时间，
            //15:30点-23:30点是中班，
            //23:30点-7:30是夜班

            //中班
//            if (hours >= 16 && hours < 24) {
            if ((hours == 15 && minutes >= 30) || (hours >= 16 && hours < 23) || (hours == 23 && minutes < 30)) {
                if (line.contains("中班带班")) {
                    map.put("lead", line.split("：")[1].trim());
                }
                //夜班
//            } else if (hours >= 8 && hours < 16) {
            } else if ((hours == 7 && minutes >= 30) || (hours >= 8 && hours < 15) || (hours == 15 && minutes < 30)) {
                if (line.contains("早班带班")) {
                    map.put("lead", line.split("：")[1].trim());
                }
            } else {
                //早班
                if (line.contains("夜班带班")) {
                    map.put("lead", line.split("：")[1].trim());
                }
            }
        }
        if (map.get("duty").toString().length() > 100 || map.get("lead").toString().length() > 100 || StringUtils.isBlank(map.get("duty").toString()) || StringUtils.isBlank(map.get("lead").toString())) {//当读取文件乱码时  获取上次的正确结果
            return RedisUtils.getCacheObject("150621011038带值班-正确结果");
        } else {
            RedisUtils.setCacheObject("150621011038带值班-正确结果", map, Duration.ofDays(7));
            return map;
        }
    }

    public Map gaotouyaoFtp() throws IOException {
        Map map = new HashMap();
        map.put("duty", "");
        map.put("lead", "");
        FTPClient ftpClient = null;//import org.apache.commons.net.ftp.FTPClient;
        BufferedReader br = null;
        InputStream is = null;
        InputStreamReader gbkReader = null;
        try {
            ftpClient = new FTPClient();
            ftpClient.setConnectTimeout(2000);
            ftpClient.setDefaultTimeout(10000);
            ftpClient.setDataTimeout(10000);

            ftpClient.setControlEncoding("GBK");
            ftpClient.connect("10.33.165.230");//连接ftp
            ftpClient.enterLocalPassiveMode();
            ftpClient.login("anonymous", null);//登陆ftp
            if (!FTPReply.isPositiveCompletion(ftpClient.getReplyCode())) {
//                throw new Exception("FTP 登录失败，响应消息:" + ftpClient.getReplyString());
            }
            ftpClient.setFileTransferMode(FTP.STREAM_TRANSFER_MODE);
            is = ftpClient.retrieveFileStream("最新.lsd");
            gbkReader = new InputStreamReader(is, "GBK");
            br = new BufferedReader(gbkReader);
            StringBuffer text = new StringBuffer();
            String content = "";
            while ((content = br.readLine()) != null) { //一次读入一行数据
                text.append(content);
            }
            ftpClient.disconnect();
            String s = text.toString();
            if (s.contains("值班领导：")) {
                String substring = s.substring(text.indexOf("值班领导：") + 5, s.indexOf("早班带班："));
                map.put("duty", substring);
            } else if (s.contains("地面带班：")) {
                map.put("duty", s.substring(s.indexOf("地面带班：") + 5, s.indexOf("早班带班：")));
            } else if (s.contains("地面值带班：")) {
                map.put("duty", s.substring(s.indexOf("地面值带班：") + 6, s.indexOf("早班带班：")));
            }


            SimpleDateFormat sdf1 = new SimpleDateFormat("HH");
            Date date = new Date();
            int hours = Integer.parseInt(sdf1.format(date));
            if (hours >= 7 && hours < 15) {
                map.put("lead", s.substring(s.indexOf("早班带班：") + 5, s.indexOf("中班带班：")));
            } else if (hours >= 15 && hours < 22) {
                map.put("lead", s.substring(s.indexOf("中班带班：") + 5, s.indexOf("夜班带班：")));
            } else {
                map.put("lead", s.substring(s.indexOf("夜班带班：") + 5, s.indexOf("值班室电话：")));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (br != null) {
                br.close();
            }
            if (gbkReader != null) {
                gbkReader.close();
            }
            if (is != null) {
                is.close();
            }

            if (ftpClient != null) {
                ftpClient.disconnect();
            }
        }
        if (map.get("duty").toString().length() > 100 || map.get("lead").toString().length() > 100 || StringUtils.isBlank(map.get("duty").toString()) || StringUtils.isBlank(map.get("lead").toString())) {//当读取文件乱码时  获取上次的正确结果
            return RedisUtils.getCacheObject("150621011038带值班-正确结果");
        } else {
            RedisUtils.setCacheObject("150621011038带值班-正确结果", map, Duration.ofDays(7));
            return map;
        }

    }

    /**
     * 获取URL文件输入流
     *
     * @param strUrl
     * @return
     */
    public static InputStream getInputStreamByUrl(String strUrl) {
        HttpURLConnection conn = null;
        try {
            URL url = new URL(strUrl);
            conn = (HttpURLConnection) url.openConnection();
            conn.setRequestMethod("GET");
            conn.setConnectTimeout(20 * 1000);
            final ByteArrayOutputStream output = new ByteArrayOutputStream();
            IOUtils.copy(conn.getInputStream(), output);
            return new ByteArrayInputStream(output.toByteArray());
        } catch (Exception e) {
            System.out.println("e = " + e);
        } finally {
            try {
                if (conn != null) {
                    conn.disconnect();
                }
            } catch (Exception e) {
                System.out.println("e = " + e);
            }
        }
        return null;
    }

    public static void main(String[] args) throws IOException, ParseException {
        FileInputStream fis = new FileInputStream("C:\\Users\\pansx\\Desktop\\文本1_0.txtf");
//        if (local.endsWith("1_0.txtf")) {
//        byte[] head = new byte[3];
//        fis.read(head);
//        }
        BufferedInputStream bis = new BufferedInputStream(fis, 1024);
        ByteArrayOutputStream bos = new ByteArrayOutputStream(1024);
        byte[] cache = new byte[10240];
        int length = 0;
        while ((length = bis.read(cache)) != -1) {
            bos.write(cache, 0, length);
        }
        String utf_16LE = new String(cache, "UTF_16LE");//1_0的文件可以
//        System.out.println(utf_16LE);
        if (bos != null) {
            bos.close();
        }
        if (bis != null) {
            bis.close();
        }
        if (fis != null) {
            fis.close();
        }
        log.info(utf_16LE);
        Pattern pat = Pattern.compile("([：][\\u4e00-\\u9fa5| ]{1,4}+)");
        Matcher mat = pat.matcher(utf_16LE);
        String str = "";
        if (mat.find()) {
            str = mat.group(0).replace("：", "");
        } else {
            Pattern pat2 = Pattern.compile("([ਰ][\\u4e00-\\u9fa5| ]{1,4}+)");
            Matcher mat2 = pat2.matcher(utf_16LE);
            if (mat2.find()) {
                str = mat2.group(0).replace("ਰ", "");
            }
        }
        System.out.println(str);
    }

    public List<TramNowPo> carNowData(String mineCode) {
//        if (StringUtils.isNotBlank(mineCode)) {
//            return locationJdbcTemplate.queryForList("select * from dwd_jxry_now_tram where mine_code='" + mineCode + "'");
//        } else {
//            return locationJdbcTemplate.queryForList("select * from dwd_jxry_now_tram");
//        }

        //查询条件构造器
        LambdaQueryWrapper<TramNowPo> wrapper = new LambdaQueryWrapper<>();
        //判断煤矿编码
        wrapper.eq(StringUtils.isNotBlank(mineCode), TramNowPo::getMineCode, mineCode);
        List<TramNowPo> tramNowPos = tramNowMapper.selectList(wrapper);
        if (tramNowPos.isEmpty()) {
            return new ArrayList<>();
        }
        return tramNowPos;
    }

    @Override
    public AreaMiningVo teamLead(String mineCode, String areaCode) {
        String sql = "";

        if ("620824009734".equals(mineCode)) {//砚北
            sql = "SELECT t2.staff_name,t2.phone,t2.duty as duty_type from \n" +
                    "(SELECT * FROM \"dwd_jxry_now_personnelinfo\" where data_time=(select max(data_time) from dwd_jxry_now_personnelinfo) and area_code='%s' ) t1 \n" +
                    "join dwd_jxry_base_personnelinfo t2 on (t1.staff_code=t2.staff_code and t1.mine_code=t2.mine_code)\n" +
                    "where  t2.dept_name like '综采%%' and t2.duty like '%%队长%%' or t2.duty like '%%班长%%' and t1.mine_code='%s'";
        } else if ("150621011038".equals(mineCode)) {//高头窑
            sql = "SELECT t2.staff_name,t2.phone,t2.duty_type from \n" +
                    "(SELECT * FROM \"dwd_jxry_now_personnelinfo\" where data_time=(select max(data_time) from dwd_jxry_now_personnelinfo) and area_code='%s' ) t1 \n" +
                    "join dwd_jxry_base_personnelinfo t2 on (t1.staff_code=t2.staff_code and t1.mine_code=t2.mine_code)\n" +
                    "where  t2.dept_name like '综采%%' and t2.duty_type like '%%队长%%' or t2.duty_type like '%%班长%%' and t1.mine_code='%s'";
        }


        List<Map<String, Object>> maps = locationJdbcTemplate.queryForList(String.format(sql, areaCode, mineCode));
        AreaMiningVo vo = new AreaMiningVo();
        for (Map<String, Object> map : maps) {
            AreaMiningVo.TeamLead lead = vo.new TeamLead();
            String duty = map.get("duty_type").toString();
            lead.setName(map.get("staff_name") + "");
            lead.setPhone(map.get("phone") + "");
            if (duty.contains("队长")) {
                vo.getCaptains().add(lead);
            } else if (duty.contains("班长")) {
                vo.getClassMonitor().add(lead);
            }

        }
        int size = peopleInfoByArea(areaCode, null, mineCode).size();
        vo.setNum(size);
        return vo;
    }

}
